<?php

/**
 * 数据库 - 快捷操作
 *
 * @author 煤老板 <meok23@sina.com>
 * @date   2017-06-01
 */

namespace fw\db;

class Quick
{
    private $_table = '';
    private $_field = '*';
    private $_order = '';
    private $_limit = '';
    private $_where = '';
    private $_prep = array();

    /**
     * 设置表名
     *
     * @param string $tblName 表名
     *
     * @return $this
     */
    public function table($tblName)
    {
        $this->_table = strtolower(trim($tblName));

        return $this; // 有这个才能串串调用，就是连贯操作啦
    }

    /**
     * 设置要查询的字段，逗号分隔
     *
     * @param string $str
     *
     * @return $this
     */
    public function field($str = '')
    {
        $str = trim($str);
        $this->_field = !empty($str) ? $str : '*';

        return $this;
    }

    /**
     * 设置 ORDER BY
     *
     * @param string $str
     *
     * @return $this
     */
    public function order($str = '')
    {
        $str = trim($str);
        $this->_order = !empty($str) ? " ORDER BY {$str}" : "";

        return $this;
    }

    /**
     * 设置 LIMIT
     *
     * @param string $str
     *
     * @return $this
     */
    public function limit($str = '')
    {
        $str = trim($str);
        $this->_limit = !empty($str) ? " LIMIT {$str}" : "";

        return $this;
    }

    /**
     * 设置 WHERE 条件与部分 prepare 参数。
     *
     * @param string $str  WHERE 条件
     * @param array  $prep 部分 prepare 参数
     *
     * @return $this
     */
    public function where($str = '', $prep = array())
    {
        $str = trim($str);
        $this->_where = !empty($str) ? " WHERE {$str}" : "";
        $this->_prep = $prep;

        return $this;
    }

    /**
     * 查询一条记录
     *
     * @return array
     */
    public function selectRow()
    {
        $sql = "SELECT {$this->_field} FROM `{$this->_table}`{$this->_where} LIMIT 0,1";

        if (!empty($this->_prep)) {
            $sth = DB::$dbh->prepare($sql);
            $sth->execute($this->_prep);
            $row = $sth->fetch(\PDO::FETCH_ASSOC);
        } else {
            $ps = DB::$dbh->query($sql);
            $row = $ps->fetch(\PDO::FETCH_ASSOC);
        }

        return $row;
    }

    /**
     * 查询所有记录
     *
     * @return array
     */
    public function select()
    {
        $sql = "SELECT {$this->_field} FROM `{$this->_table}`{$this->_where}{$this->_order}{$this->_limit}";

        if (!empty($this->_prep)) {
            $sth = DB::$dbh->prepare($sql);
            $sth->execute($this->_prep);
            $rows = $sth->fetchAll(\PDO::FETCH_ASSOC);
        } else {
            $ps = DB::$dbh->query($sql);
            $rows = $ps->fetchAll(\PDO::FETCH_ASSOC);
        }

        return $rows;
    }

    /**
     * 查询某个字段
     *
     * @param string $col
     * @param bool   $multi
     *
     * @return array|mixed
     */
    public function selectCol($col, $multi = true)
    {
        $sql = "SELECT {$col} FROM `{$this->_table}`{$this->_where}";

        if (true !== $multi) {
            $sql = $sql . '0,1';
            $ps = DB::$dbh->query($sql);
            $ret = $ps->fetch(\PDO::FETCH_COLUMN);
        } else {
            $sql = $sql . $this->_limit;
            $ps = DB::$dbh->query($sql);
            $ret = $ps->fetchAll(\PDO::FETCH_COLUMN);
        }

        return $ret;
    }

    /**
     * 返回记录行数
     *
     * @return int
     */
    public function count()
    {
        $sql = "SELECT count(*) as cc FROM `{$this->_table}`{$this->_where}";

        if (!empty($this->_prep)) {
            $sth = DB::$dbh->prepare($sql);
            $sth->execute($this->_prep);
            $ret = $sth->fetch(\PDO::FETCH_COLUMN);
        } else {
            $ps = DB::$dbh->query($sql);
            $ret = $ps->fetch(\PDO::FETCH_COLUMN);
        }

        return (int)$ret;
    }

    /**
     * 插入一条记录。
     * 自增主键插入成功返回 id值，其它情况插入成功返回 true，失败返回false
     *
     * @param array  $data 要插入数的数据
     * @param string $type 区分 INSERT INTO | INSERT IGNORE INTO | REPLACE INTO
     *
     * @return bool|int
     * @throws Exception
     */
    public function insert($data, $type = 'INSERT')
    {
        $keys = '`' . join('`,`', array_keys($data)) . '`';
        $vals = ':' . join(',:', array_keys($data));
        $param = array();

        foreach ($data as $f => $v) {
            $param[':' . $f] = $v;
        }

        $type = strtoupper(trim($type));
        switch ($type) {
            case 'INSERT':
                $insert_into = "INSERT INTO";
                break;
            case 'IGNORE':
                $insert_into = "INSERT IGNORE INTO";
                break;
            case 'REPLACE':
                $insert_into = "REPLACE INTO";
                break;
            default:
                $insert_into = "INSERT INTO";
                break;
        }

        $sql = "{$insert_into} `{$this->_table}` ({$keys}) VALUES ({$vals})";

        // $data 不能为空
        if (empty($data)) {
            throw new Exception("Require data param! The sql [{$sql}]");
        }

        $sth = DB::$dbh->prepare($sql);
        $sth->execute($param);
        $id = DB::$dbh->lastInsertId();

        $count = $sth->rowCount();
        if ((int)$count < 1) {
            // 插入失败
            return false;
        }

        // 自增主键插入成功返回 id值，其它情况插入成功返回 true
        return ($id > 0) ? $id : true;
    }

    /**
     * 更新表记录。
     * 成功返回影响行数，失败返回false
     *
     * @param array $data
     *
     * @return bool|int
     * @throws Exception
     */
    public function update($data)
    {
        // 参数准备
        $set = '';
        $param = array();

        foreach ($data as $f => $v) {
            $set .= ',' . $f . '=:' . $f;
            $param[':' . $f] = $v;
        }
        $set = substr($set, 1);

        if (!empty($this->_prep)) {
            $param = array_merge($param, $this->_prep);
        }

        $sql = "UPDATE `{$this->_table}` SET {$set}{$this->_where}{$this->_limit}";

        // 必须指定where条件
        if (empty($this->_where)) {
            throw new Exception("Require where condition! The sql [{$sql}]");
        }

        // $data 不能为空
        if (empty($data)) {
            throw new Exception("Require data param! The sql [{$sql}]");
        }

        $sth = DB::$dbh->prepare($sql);
        $sth->execute($param);
        $affected_rows = $sth->rowCount();

        return ((int)$affected_rows < 1) ? false : (int)$affected_rows;
    }

    /**
     * 字段递增
     *
     * @param $col
     *
     * @return bool|int
     */
    public function incr($col)
    {
        // 参数准备
        $col = trim($col);

        $sql = "UPDATE `{$this->_table}` SET `{$col}`=`{$col}`+1 {$this->_where} LIMIT 1";
        $affected_rows = DB::$dbh->exec($sql);

        return ((int)$affected_rows < 1) ? false : (int)$affected_rows;
    }

    /**
     * 删除记录。
     * 成功返回影响行数，失败返回false
     *
     * @return bool|int
     * @throws Exception
     */
    public function delete()
    {
        $sql = "DELETE FROM `{$this->_table}`{$this->_where}{$this->_limit}";

        // 必须指定where条件
        if (empty($this->_where)) {
            throw new Exception("Require where condition! The sql [{$sql}]");
        }

        if (!empty($this->_prep)) {
            $sth = DB::$dbh->prepare($sql);
            $sth->execute($this->_prep);
            $affected_rows = $sth->rowCount();
        } else {
            $affected_rows = DB::$dbh->exec($sql);
        }

        return ((int)$affected_rows < 1) ? false : (int)$affected_rows;
    }
}
